import java.awt.Color;
import java.awt.Dimension;
import java.awt.Graphics;
import java.awt.Image;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

import javax.swing.ImageIcon;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLayeredPane;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.ListSelectionModel;
import javax.swing.table.DefaultTableModel;

import java.sql.*;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;


/*
 * NOTE: ADD DEDUCTIONS AND ADD ADDITIONAL PAYMENTS WILL ONLY SELECT THE LATEST ENTRY BASED ON DATESAL
 *  because of deductionbonus.dateSal = (select max(deductionbonus.dateSal) from deductionbonus) in query
 */

public class EditPayrollForm extends JFrame{
	private static final long serialVersionUID = 1L;
	private JTextField userID;
	private JButton search, addDeduc, addExPay, cancel, submit, submit1;
	private JPanel edPanel;
	private DeductionsForm dedPanel = new DeductionsForm();
	private BonusesForm bonPanel = new BonusesForm();
	private static bgPane bgPanel = new bgPane();
	private int uID, pId;
	private JTable tableMod;
	private DefaultTableModel dedbonList;


	public EditPayrollForm() {
		setTitle("Edit Payroll");
		JLayeredPane paylay = new JLayeredPane();

		edPanel =  new JPanel();

		//construct components
		userID = new JTextField (5);
		search = new JButton ("Search");
		addDeduc = new JButton ("Add Deductions");
		addExPay = new JButton ("Add Extra Payments");
		cancel = new JButton ("Cancel");
		submit = new JButton ("Submit");
		submit1 = new JButton("Submit");

		//construction of table

		String[] columnNames = {"Name", "Amount", "Type", "Reason", "Date"};
		tableMod = new JTable();
		tableMod.setModel(new javax.swing.table.DefaultTableModel(null, columnNames){
			private static final long serialVersionUID = 1L;
			public boolean isCellEditable(int rowIndex, int columnIndex) {
				return false;
			}
		});
		//tableMod.setFillsViewportHeight(true);
		tableMod.getTableHeader().setReorderingAllowed(false);
		tableMod.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
		//	tableMod.setRowSelectionAllowed(true);
		dedbonList = (DefaultTableModel)tableMod.getModel();
		JScrollPane	scrollPane = new JScrollPane();
		scrollPane.setViewportView(tableMod);

		//adjust size and set layout
		setPreferredSize (new Dimension (440, 350));
		setLayout (null);

		//add components
		edPanel.add(userID);
		edPanel.add(search);
		edPanel.add(addDeduc);
		edPanel.add(addExPay);
		edPanel.add(scrollPane);

		paylay.setOpaque(false);
		paylay.setSize(440, 350);
		paylay.add(edPanel);
		paylay.add(dedPanel);
		paylay.add(bonPanel);
		paylay.add(cancel);
		paylay.add(submit);
		paylay.add(submit1);
		paylay.add(bgPanel);
		paylay.setVisible(true);

		add(paylay);

		edPanel.setSize(440,350);
		edPanel.setOpaque(false);
		edPanel.setLayout(null);

		//set component bounds (only needed by Absolute Positioning)
		userID.setBounds (25, 75, 165, 30);
		search.setBounds (195, 75, 100, 30);
		addDeduc.setBounds (25, 265, 125, 25);
		addExPay.setBounds (155, 265, 150, 25);
		cancel.setBounds (310, 265, 100, 25);
		submit.setBounds (175, 265, 125, 25);
		submit1.setBounds (175, 265, 125, 25);
		scrollPane.setBounds(25, 110, 385, 150);

		submit.setForeground(Color.WHITE);
		cancel.setForeground(Color.WHITE);
		submit1.setForeground(Color.WHITE);
		search.setForeground(Color.WHITE);
		addDeduc.setForeground(Color.WHITE);
		addExPay.setForeground(Color.WHITE);
		search.setBackground(Color.BLACK);
		addDeduc.setBackground(Color.BLACK);
		addExPay.setBackground(Color.BLACK);
		cancel.setBackground(Color.BLACK);
		submit.setBackground(Color.BLACK);
		submit1.setBackground(Color.BLACK);

		dedPanel.setVisible(false);
		bonPanel.setVisible(false);
		edPanel.setVisible(true);
		submit.setVisible(false);
		submit1.setVisible(false);



		//action listeners
		addDeduc.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e){
				if(uID == 0)
					JOptionPane.showMessageDialog(dedPanel, "No employee is selected.", "Error", JOptionPane.ERROR_MESSAGE );
				else{
					try{
						ConnectionFactory myFactory = ConnectionFactory.getFactory();
						Connection conn = myFactory.getConnection();

						edPanel.setVisible(false);
						float amt = 0;
						NumberFormat nf = NumberFormat.getInstance();
						nf.setMaximumFractionDigits(2);            
						nf.setGroupingUsed(false);
						PreparedStatement getcursal; //GETS ONLY THE SUM FROM THE LATEST DATE ENTRY
						getcursal = conn.prepareStatement("SELECT SUM(amount) as 'Current Deductions' FROM deductionbonus, payroll where deductionbonus.idpayroll = payroll.idpayroll and payroll.idpayroll='" + pId
								+ "' and ptype = '1' and deductionbonus.dateSal = (select max(deductionbonus.dateSal) from deductionbonus where deductionbonus.idpayroll ='" + pId + "' and ptype = '1') group  by deductionbonus.idpayroll");
						ResultSet curde = getcursal.executeQuery();

						if(curde.next())
						{
							amt = curde.getFloat("Current Deductions");
							dedPanel.setuID(uID);
							dedPanel.setCurDeduc(String.valueOf(nf.format(amt)));
						}

						dedPanel.setVisible(true);
						submit.setVisible(true);

						conn.close();
					}catch(Exception ex){
						ex.printStackTrace();
					}
				}
			}
		});

		addExPay.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e){
				if(uID == 0)
					JOptionPane.showMessageDialog(null, "No employee is selected.", "Error", JOptionPane.ERROR_MESSAGE );
				else{
					try{
						ConnectionFactory myFactory = ConnectionFactory.getFactory();
						Connection conn = myFactory.getConnection();

						edPanel.setVisible(false);
						float amt = 0;
						NumberFormat nf = NumberFormat.getInstance();
						nf.setMaximumFractionDigits(2);            
						nf.setGroupingUsed(false);
						PreparedStatement getcursal;
						getcursal = conn.prepareStatement("SELECT SUM(amount) as 'Current Bonus' FROM deductionbonus, payroll where deductionbonus.idpayroll = payroll.idpayroll and payroll.idpayroll='" + pId
								+ "' and ptype = '0' and deductionbonus.dateSal = (select max(deductionbonus.dateSal) from deductionbonus where deductionbonus.idpayroll ='" + pId + "' and ptype='0') group by deductionbonus.idpayroll");
						ResultSet curde = getcursal.executeQuery();

						if(curde.next())
						{
							amt = curde.getFloat("Current Bonus");
							bonPanel.setuID(uID);
							bonPanel.setCurAddit(String.valueOf(nf.format(amt)));
						}

						bonPanel.setVisible(true);
						submit1.setVisible(true);

						conn.close();
					}catch(Exception ex){
						ex.printStackTrace();
					}
				}
			}
		});

		cancel.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e){
				setVisible(false);
			}
		});


		//deductions button
		submit.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e){
				if(dedPanel.getDeducReason().equals("inc"))
					JOptionPane.showMessageDialog(null, "Reason for added deduction is required.", "Error", JOptionPane.ERROR_MESSAGE );
				else if(dedPanel.getNewDeduc().length() == 0 || dedPanel.getDeducReason().equals("invalid"))
					JOptionPane.showMessageDialog(null, "All fields must be filled out.", "Error", JOptionPane.ERROR_MESSAGE );
				else{
					setVisible(false);
					try{
						ConnectionFactory myFactory = ConnectionFactory.getFactory();
						Connection conn = myFactory.getConnection();

						PreparedStatement addDed;
						addDed = conn.prepareStatement("INSERT INTO deductionbonus (dateSal, pType, amount, reason, idpayroll) VALUES(?, '1', ?, ?, ?)");
						addDed.setString(1, now("yyyy-MM-dd"));
						addDed.setString(2, dedPanel.getNewDeduc());
						addDed.setString(3, dedPanel.getDeducReason());
						addDed.setInt(4, pId);
						addDed.executeUpdate();
						JOptionPane.showMessageDialog(dedPanel, "You have successfully added a salary deduction.");
						AdminForm.goToPage(0);

						conn.close();
					}catch(Exception ex){
						ex.printStackTrace();
					}
				}
			}
		});

		//bonuses button
		submit1.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e){
				if(bonPanel.getNewAddit().length() == 0 || bonPanel.getReason().equals("invalid"))
					JOptionPane.showMessageDialog(null, "All fields must be filled out.", "Error", JOptionPane.ERROR_MESSAGE );
				else{
					setVisible(false);
					try{
						ConnectionFactory myFactory = ConnectionFactory.getFactory();
						Connection conn = myFactory.getConnection();

						PreparedStatement addDed;
						addDed = conn.prepareStatement("INSERT INTO deductionbonus (dateSal, pType, amount, reason, idpayroll) VALUES(?, '0', ?, ?, ?)");
						addDed.setString(1, now("yyyy-MM-dd"));
						addDed.setString(2, bonPanel.getNewAddit());
						addDed.setString(3, bonPanel.getReason());
						addDed.setInt(4, pId);
						addDed.executeUpdate();
						JOptionPane.showMessageDialog(bonPanel, "You have successfully added a salary bonus.");
						AdminForm.goToPage(0);

						conn.close();
					}catch(Exception ex){
						ex.printStackTrace();
					}
				}
			}
		});

		//search button
		search.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e){
				try{

					if(getUserid().length() == 0){
						JOptionPane.showMessageDialog(edPanel, "No name was entered. Please try again.", "Error", JOptionPane.ERROR_MESSAGE);
					}
					else{
						ConnectionFactory myFactory = ConnectionFactory.getFactory();
						Connection conn = myFactory.getConnection();

						Statement stat = conn.createStatement();
						ResultSet sea = null;

						//search by name
						String[] names = null;
						String uName = getUserid();
						String name1, name2, name3, name4, name5;

						names = uName.split(" ",0);
						name1 = names[0];
						if (name1.contains(","))
							name1 = name1.split(",")[0];
						if(names.length==1)
							JOptionPane.showMessageDialog(null, "Please enter both first and last name.", "Error", JOptionPane.ERROR_MESSAGE);
						//sea = stat.executeQuery("SELECT idemployee from employee where firstName like '"+name1+"%' or lastName like'"+name1+"%'"); 
						else if (names.length==2)	{
							name2 = names[1];
							if (name1.contains(","))
								name1 = name1.split(",")[0];
							sea = stat.executeQuery("SELECT idemployee from employee where (firstName like '"+name1+"%'and lastName like '"+name2+"%')"+
									" or (lastName like '"+name1+"%'and firstName like '"+name2+"%') or (firstName like '"+name1.concat(" ").concat(name2)+"%')"+"or (lastName like '"+name1.concat(" ").concat(name2)+"%')"+
									" or (firstName like '"+name1+"%' and middleInitial='"+name2+"')");
						} else if (names.length==3) { 
							name2 = names[1];
							name3 = names[2];
							if (name1.contains(","))
								name1 = name1.split(",")[0];
							else if (name2.contains(","))
								name2 = name2.split(",")[0];
							sea = stat.executeQuery("SELECT idemployee from employee"+
									" where (firstName like '"+name1+"%' and middleInitial ='"+name2+"' and lastName like '"+name3+"%')"+
									" or (lastName like '"+name1+"%'and firstName like '"+name2+"%' and middleInitial='"+name3+"')"+
									" or (firstName like '"+name1.concat(" ").concat(name2)+"%' and middleInitial='"+name3+"') or (firstName like '"+name1.concat(" ").concat(name2)+"%' and lastName like '"+name3+"%') or (firstName like '"+name1+"%' and lastName like '"+name2.concat(" ").concat(name3)+"%')"+
									" or (lastName like '"+name1.concat(" ").concat(name2)+"%' and firstName like '"+name3+"%') or (lastName like '"+name1+"%' and firstName like '"+name2.concat(" ").concat(name3)+"%')");
						} else if (names.length==4) { 
							name2 = names[1];
							name3 = names[2];
							name4 = names[3];
							if (name1.contains(","))
								name1 = name1.split(",")[0];
							else if (name2.contains(","))
								name2 = name2.split(",")[0];
							sea = stat.executeQuery("SELECT idemployee from employee"+
									" where (firstName like '"+name1.concat(" ").concat(name2)+"%' and middleInitial ='"+name3+"' and lastName like '"+name4+"%') or (firstName like '"+name1+"%' and middleInitial ='"+name2+"' and lastName like '"+name3.concat(" ").concat(name4)+"%')"+
									" or (firstName like '"+name1.concat(" ").concat(name2)+"%' and lastName like '"+name3.concat(" ").concat(name4)+"%') or (lastName like '"+name1.concat(" ").concat(name2)+"%' and firstName like '"+name3.concat(" ").concat(name4)+"%')"+
									" or (lastName like '"+name1.concat(" ").concat(name2)+"%' and firstName like '"+name3+"%' and middleInitial='"+name4+"') or (lastName like '"+name1+"%' and firstName like '"+name2.concat(" ").concat(name3)+"%' and middleInitial='"+name4+"')");
						} else { 
							name2 = names[1];
							name3 = names[2];
							name4 = names[3];
							name5 = names[4];
							if (name2.contains(","))
								name2 = name2.split(",")[0];
							sea = stat.executeQuery("SELECT idemployee from bernalcompany.employee e"+
									" where (firstName like '"+name1.concat(" ").concat(name2)+"%' and middleInitial ='"+name3+"' and lastName like '"+name4.concat(" ").concat(name5)+"%')"+
									" or (lastName like '"+name1.concat(" ").concat(name2)+"%' and middleInitial ='"+name3+"' and firstName like '"+name4.concat(" ").concat(name5)+"%')");
						}

						if(names.length>1)
						if(sea.next()){
							uID = sea.getInt("idemployee");
							PreparedStatement getpid;
							getpid = conn.prepareStatement("SELECT idpayroll FROM employee, payroll WHERE payroll.idemployee ='" + uID + "' and dateSal = (select max(dateSal) from payroll)");
							ResultSet res = getpid.executeQuery();					
							if(res.next()){
								pId = res.getInt("idpayroll");
							listUpdate();
						}
						else
							JOptionPane.showMessageDialog(edPanel, "Name is not in the database. Please try again.", "Error", JOptionPane.ERROR_MESSAGE);
						}
						conn.close();
					}
				}catch(Exception ex){
					ex.printStackTrace();
				}
			}
		});

	}

	public String getUserid() {
		return userID.getText().trim();
	}

	public static String now (String dateFormat){

		Calendar cal = Calendar.getInstance();
		SimpleDateFormat dateTime = new SimpleDateFormat(dateFormat);
		return dateTime.format(cal.getTime());
	}


	public void listUpdate(){

		try
		{
			NumberFormat nf = NumberFormat.getInstance();
			nf.setMaximumFractionDigits(2);            
			nf.setGroupingUsed(false); 

			ConnectionFactory myFactory = ConnectionFactory.getFactory();
			Connection conn = myFactory.getConnection();
			PreparedStatement preStat = (PreparedStatement) conn.prepareStatement("SELECT concat(firstName, ' ', middleInitial, ' ', lastName) as 'Full Name', "+
					"amount, CASE ptype when 1 then 'Deduction' when 0 then 'Bonus' END as 'type', reason, deductionbonus.dateSal as 'date' from deductionbonus, employee, payroll " +
					"where payroll.idpayroll = deductionbonus.idpayroll and payroll.idpayroll='" + pId + "' and employee.idemployee ='"+ uID +"' and deductionbonus.dateSal = (select max(deductionbonus.dateSal) from deductionbonus where payroll.idpayroll = deductionbonus.idpayroll)");
			ResultSet getDet = preStat.executeQuery();

			dedbonList.getDataVector().removeAllElements();
			tableMod.setAutoCreateRowSorter(true);

			while(getDet.next()){
				dedbonList.addRow(new Object[]{ getDet.getString("Full Name"), nf.format(getDet.getFloat("amount")), getDet.getString("type"), getDet.getString("reason"), getDet.getString("date")});
				dedbonList.fireTableDataChanged();
			}

			PreparedStatement totA = conn.prepareStatement("SELECT (count2.bon - count1.deduct) as 'Total' FROM deductionbonus, payroll, " +
					"(SELECT SUM(amount) as 'deduct' FROM deductionbonus, payroll WHERE deductionbonus.idpayroll = payroll.idpayroll and payroll.idpayroll = '"+ pId +"' and ptype = '1') as count1, "+
					"(SELECT SUM(amount) as 'bon' FROM deductionbonus, payroll WHERE deductionbonus.idpayroll = payroll.idpayroll and payroll.idpayroll = '"+ pId +"' and ptype = '0') as count2 " +
					"where payroll.idpayroll ='"+ pId +"'group by payroll.idpayroll");
			ResultSet getTo = totA.executeQuery();
			if(getTo.next()){
				float tempTot = getTo.getFloat("Total");
				if( tempTot < 0){
					tempTot *= -1;
					dedbonList.addRow(new Object[]{ "Total Deduction:", nf.format(tempTot)});
				}
				else
					dedbonList.addRow(new Object[]{ "Total Bonus:", nf.format(tempTot)});
				dedbonList.fireTableDataChanged();
			}

			getTo.close();
			preStat.close();

		}catch(Exception ex){
			ex.printStackTrace();
		}

	}

	public int getCheckSpace(){

		String uName = userID.getText();

		char[] unameArray = uName.toCharArray();

		int i = 0;
		int lenSpace = uName.length();

		while ( lenSpace > 0)
		{ 	char c = unameArray[i];

		if ( c == ' '){
			//may space!
			return 1;
		}
		else
			lenSpace--;
		i++;
		}
		return 0;
	}

}

class bgPane extends JPanel {
	private static final long serialVersionUID = 1L;
	private  ImageIcon ii = new ImageIcon(this.getClass().getResource("images/editpayrollbg.png"));
	private Image bgimage = ii.getImage();

	public bgPane() {
		setBounds(0,0,800,600);
	}

	public void paintComponent(Graphics g) {
		g.drawImage(bgimage,0,0, this);
	}
}



